-- PostgreSQL SQL script for doc module tables
-- This script creates tables in both public and vectors schemas

-- First, create the vectors schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS vectors;

-- Enable the pgvector extension if not already enabled
CREATE EXTENSION IF NOT EXISTS vector;

-- PUBLIC SCHEMA TABLES

-- Document Category Table
CREATE TABLE IF NOT EXISTS public.doc_category (
    category_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id BIGINT,
    order_num INTEGER,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_parent_category FOREIGN KEY (parent_id) REFERENCES public.doc_category(category_id)
);

-- Document Table
CREATE TABLE IF NOT EXISTS public.sys_document (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    category_id BIGINT,
    status INTEGER DEFAULT 0,
    version VARCHAR(50),
    creator_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    del_flag INTEGER DEFAULT 0,
    CONSTRAINT fk_document_category FOREIGN KEY (category_id) REFERENCES public.doc_category(category_id)
);

-- Chat Session Table
CREATE TABLE IF NOT EXISTS public.sys_chat_session (
    id BIGSERIAL PRIMARY KEY,
    session_id VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT,
    title VARCHAR(200),
    last_message TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Chat History Table (without vector embeddings)
CREATE TABLE IF NOT EXISTS public.sys_chat_history (
    id BIGSERIAL PRIMARY KEY,
    session_id VARCHAR(64) NOT NULL,
    user_id BIGINT,
    role VARCHAR(20) NOT NULL,
    content TEXT,
    tokens INTEGER,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_chat_session FOREIGN KEY (session_id) REFERENCES public.sys_chat_session(session_id)
);

-- Enum Config Table
CREATE TABLE IF NOT EXISTS public.sys_enum_config (
    id BIGSERIAL PRIMARY KEY,
    enum_key VARCHAR(100) NOT NULL,
    enum_value VARCHAR(200) NOT NULL,
    enum_type VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    order_num INTEGER,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Prompt Template Table
CREATE TABLE IF NOT EXISTS public.sys_prompt_template (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    category VARCHAR(100),
    tags TEXT[],
    usage_count BIGINT DEFAULT 0,
    creator_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- VECTORS SCHEMA TABLES

-- Document Vectors Table
CREATE TABLE IF NOT EXISTS vectors.document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT NOT NULL,
    chunk_index INTEGER,
    chunk_text TEXT,
    embedding vector(1536),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_document FOREIGN KEY (document_id) REFERENCES public.sys_document(id) ON DELETE CASCADE
);

-- Chat History Vectors Table
CREATE TABLE IF NOT EXISTS vectors.chat_history_embeddings (
    id BIGSERIAL PRIMARY KEY,
    chat_history_id BIGINT NOT NULL,
    embedding vector(1536),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_chat_history FOREIGN KEY (chat_history_id) REFERENCES public.sys_chat_history(id) ON DELETE CASCADE
);

-- Prompt Template Vectors Table
CREATE TABLE IF NOT EXISTS vectors.prompt_template_embeddings (
    id BIGSERIAL PRIMARY KEY,
    template_id BIGINT NOT NULL,
    embedding vector(1536),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_prompt_template FOREIGN KEY (template_id) REFERENCES public.sys_prompt_template(id) ON DELETE CASCADE
);

-- Create indexes for vector similarity search
CREATE INDEX IF NOT EXISTS idx_document_embedding ON vectors.document_embeddings USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS idx_chat_history_embedding ON vectors.chat_history_embeddings USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS idx_prompt_template_embedding ON vectors.prompt_template_embeddings USING ivfflat (embedding vector_cosine_ops);

-- Insert some sample enum data
INSERT INTO public.sys_enum_config (enum_key, enum_value, enum_type, description, order_num)
VALUES 
('DOCUMENT_STATUS_DRAFT', '0', 'DOCUMENT_STATUS', '草稿', 1),
('DOCUMENT_STATUS_PUBLISHED', '1', 'DOCUMENT_STATUS', '已发布', 2),
('DOCUMENT_STATUS_ARCHIVED', '2', 'DOCUMENT_STATUS', '已归档', 3);

-- Insert root document category
INSERT INTO public.doc_category (name, parent_id, order_num)
VALUES ('根目录', NULL, 1);

-- Create functions for vector similarity search
CREATE OR REPLACE FUNCTION vectors.search_similar_documents(query_embedding vector, similarity_threshold float, max_results int)
RETURNS TABLE (
    document_id bigint,
    title varchar,
    similarity float
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.id,
        d.title,
        1 - (de.embedding <=> query_embedding) AS similarity
    FROM 
        vectors.document_embeddings de
    JOIN 
        public.sys_document d ON de.document_id = d.id
    WHERE 
        1 - (de.embedding <=> query_embedding) > similarity_threshold
    ORDER BY 
        similarity DESC
    LIMIT max_results;
END;
$$ LANGUAGE plpgsql;

-- Create function to update vector embeddings when documents are updated
CREATE OR REPLACE FUNCTION public.update_document_embedding()
RETURNS TRIGGER AS $$
BEGIN
    -- This is a placeholder. In a real implementation, you would call an external 
    -- service to generate the embedding and then update the vectors table
    -- For now, we'll just delete old embeddings to maintain referential integrity
    DELETE FROM vectors.document_embeddings WHERE document_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for document updates
CREATE OR REPLACE TRIGGER trigger_update_document_embedding
AFTER UPDATE ON public.sys_document
FOR EACH ROW
WHEN (OLD.content IS DISTINCT FROM NEW.content OR OLD.title IS DISTINCT FROM NEW.title)
EXECUTE FUNCTION public.update_document_embedding();
